package by.epam.communalservices.utils;

import java.sql.*;
import java.util.*;

import org.apache.log4j.Logger;

import by.epam.communalservices.entity.User;
import by.epam.communalservices.exception.JkhDaoException;

public class Login {

    private Long id;

    private String login;

    private String password;

    private Set<String> setRoles;

    private final String COLUMN_ROLE_NAME = "role_name";

    // private final String COLUMN_PASSWD = "password";

    // private final String COLUMN_LOGIN = "login";

    private final String COLUMN_USER_ID = "user_id";

    private Connection con;

    static Logger log = Logger.getLogger(Login.class.getName());

    public Login(Connection con) throws JkhDaoException {
        try {
            if (!con.isClosed())
                this.con = con;
            else {
                throw new JkhDaoException("Connection error. Connection is not open", new Exception());
            }
        } catch (JkhDaoException jex) {
            throw jex;
        } catch (SQLException sx) {
            sx.printStackTrace();
            throw new JkhDaoException("Unknown connection error", new Exception());

        }
    }

    public void setUser(User user) {
        id = user.getId();
        login = user.getLogin();
        password = user.getPassw();
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getLogin() {
        return login;
    }

    public void setLogin(String login) {
        this.login = login;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    @SuppressWarnings("unchecked")
    public Set<String> getRole() {
        setRoles = Collections.EMPTY_SET;
        if (id==0L) {
            return setRoles;
        }
        log.debug("List<User> findAll()");
        log.info("method returns a list of users");
        PreparedStatement pstate = null;
        ResultSet result = null;
        try {
            try {
                String sql = "SELECT public.role.role_name from public.role where " + "public.role.role_user_id = " + id + " ";
                pstate = con.prepareStatement(sql);
                result = pstate.executeQuery();
                setRoles = new HashSet<String>();
                while (result.next()) {
                    setRoles.add(result.getString(COLUMN_ROLE_NAME));
                }
            } catch (SQLException ex) {
                log.error("Error compiling the list", ex);
                ex.printStackTrace(System.out);
            }
        } finally {
            if (result != null)
                try {
                    result.close();
                } catch (SQLException ex) {
                    log.error("Error close result", ex);
                    ex.printStackTrace();
                }
            if (pstate != null)
                try {
                    pstate.close();
                } catch (SQLException ex) {
                    log.error("Error close statement", ex);
                    ex.printStackTrace();
                }
        }
        return setRoles;
    }

    @SuppressWarnings("unchecked")
    public Set<String> getRole(Long id) {
        setRoles = Collections.EMPTY_SET;
        log.debug("List<User> findAll()");
        log.info("method returns a list of users");
        PreparedStatement pstate = null;
        ResultSet result = null;
        try {
            try {
                String sql = "SELECT public.role.role_name from public.role where " + "public.role.role_user_id = " + id + " ";
                pstate = con.prepareStatement(sql);
                result = pstate.executeQuery();
                setRoles = new HashSet<String>();
                while (result.next()) {
                    setRoles.add(result.getString(COLUMN_ROLE_NAME));
                }
            } catch (SQLException ex) {
                log.error("Error compiling the list", ex);
                ex.printStackTrace(System.out);
            }
        } finally {
            if (result != null)
                try {
                    result.close();
                } catch (SQLException ex) {
                    log.error("Error close result", ex);
                    ex.printStackTrace();
                }
            if (pstate != null)
                try {
                    pstate.close();
                } catch (SQLException ex) {
                    log.error("Error close statement", ex);
                    ex.printStackTrace();
                }
        }
        return setRoles;
    }

    public void deleteRole() {
        log.debug("setRole(Set<String> setRoles)");
        log.info("method sets the role to a user");
        String sql = "DELETE FROM public.role WHERE public.role.role_user_id  = ?";
        ResultSet result = null;
        PreparedStatement pstate = null;
        try {
            try {
                pstate = con.prepareStatement(sql);
                pstate.setLong(1, id);
                result = pstate.executeQuery();
            } catch (SQLException ex) {
                log.error("query execute error", ex);
                ex.printStackTrace();
            }
        } finally {
            if (result != null)
                try {
                    result.close();
                } catch (SQLException ex) {
                    log.error("Error close result", ex);
                    ex.printStackTrace();
                }
            if (pstate != null)
                try {
                    pstate.close();
                } catch (SQLException ex) {
                    log.error("Error close statement", ex);
                    ex.printStackTrace();
                }
        }
    }

    public void setRole(Set<String> setRoles) {
        this.setRoles = setRoles;
        log.debug("setRole(Set<String> setRoles)");
        log.info("method sets the role to a user");
        String sql = "INSERT INTO public.role (role_name,role_user_id) VALUES (?,?)";
        ResultSet result = null;
        PreparedStatement pstate = null;
        try {
            if (setRoles != null) {
                for (String s : setRoles) {
                    try {
                        pstate = con.prepareStatement(sql);
                        pstate.setString(1, s);
                        pstate.setLong(2, id);
                        result = pstate.executeQuery();
                    } catch (SQLException ex) {
                        log.error("query execute error", ex);
                        ex.printStackTrace();
                    }
                }
            }
        } finally {
            if (result != null)
                try {
                    result.close();
                } catch (SQLException ex) {
                    log.error("Error close result", ex);
                    ex.printStackTrace();
                }
            if (pstate != null)
                try {
                    pstate.close();
                } catch (SQLException ex) {
                    log.error("Error close statement", ex);
                    ex.printStackTrace();
                }
        }
    }

    public User getUser() throws JkhDaoException {

        if (login == null || password == null)
            return null;
        log.debug("User read(Long id)");
        log.info("method reads the user");
        String sql = "SELECT user_id FROM public.user WHERE login = ? and password = ? ";
        User usr = null;
        ResultSet result = null;
        PreparedStatement pstate = null;
        try {
            try {
                pstate = con.prepareStatement(sql);
                pstate.setString(1, login);
                pstate.setString(2, password);
                result = pstate.executeQuery();
            } catch (SQLException ex) {
                log.error("query execute error", ex);
                ex.printStackTrace();
            }
            try {
                if (result.next()) {
                    usr = new User(result.getLong(COLUMN_USER_ID), login, password);
                    setId(result.getLong(COLUMN_USER_ID));
                } else {
                    throw new SQLException();
                }
            } catch (SQLException ex) {
                log.error("Record not found", ex);
                ex.printStackTrace();
            }
        } finally {
            if (result != null)
                try {
                    result.close();
                } catch (SQLException ex) {
                    log.error("Error close result", ex);
                    ex.printStackTrace();
                }
            if (pstate != null)
                try {
                    pstate.close();
                } catch (SQLException ex) {
                    log.error("Error close statement", ex);
                    ex.printStackTrace();
                }
        }
        this.id = usr.getId();
        return usr;
    }

    public User getUser(String login, String password) throws JkhDaoException {
        this.login = login;
        this.password = password;
        return getUser();
    }

}
